package com.CommunityHub.kiana.dao;



import com.CommunityHub.kiana.po.HouseInfo;
import com.CommunityHub.kiana.utils.DBUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class HouseInfoDAO {
    public List<HouseInfo> findByStatus(int status) throws SQLException {
        List<HouseInfo> houses = new ArrayList<>();
        String sql = "SELECT * FROM house_info WHERE status = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, status);
            rs = ps.executeQuery();

            while (rs.next()) {
                HouseInfo house = mapHouse(rs);
                house.setHouseImage(rs.getString("house_image"));
                houses.add(house);
            }
            return houses;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 根据买家ID查询房屋
    public List<HouseInfo> findByBuyerId(int buyerId) throws SQLException {
        List<HouseInfo> houses = new ArrayList<>();
        String sql = "SELECT * FROM house_info WHERE buyer_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, buyerId);
            rs = ps.executeQuery();

            while (rs.next()) {
                HouseInfo house = mapHouse(rs);
                houses.add(house);
            }
            return houses;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 检查会员是否已有房产
    public boolean hasPurchasedHouse(int memberId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM house_info WHERE buyer_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 购买房产
    public boolean purchaseHouse(int houseId, int memberId) throws SQLException {
        String sql = "UPDATE house_info SET status=1, buyer_id=? WHERE house_id=? AND status=0";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            ps.setInt(2, houseId);

            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }

    // 检查房屋所有权
    public boolean isHouseOwner(int houseId, int memberId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM house_info WHERE house_id=? AND buyer_id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, houseId);
            ps.setInt(2, memberId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    private HouseInfo mapHouse(ResultSet rs) throws SQLException {
        HouseInfo house = new HouseInfo();
        house.setHouseId(rs.getInt("house_id"));
        house.setHouseName(rs.getString("house_name"));
        house.setBuildingNumber(rs.getString("building_number"));
        house.setUnitNumber(rs.getString("unit_number"));
        house.setRoomNumber(rs.getString("room_number"));
        house.setArea(rs.getBigDecimal("area"));
        house.setPrice(rs.getBigDecimal("price"));
        house.setStatus(rs.getInt("status"));
        house.setBuyerId(rs.getInt("buyer_id"));
        house.setHouseImage(rs.getString("house_image"));
        return house;
    }


    /**
     * 根据筛选条件查询房屋
     */
    public List<HouseInfo> findHousesByFilters(String priceRange, String areaRange, String location)
            throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM house_info WHERE status = 0");
        List<Object> params = new ArrayList<>();

        /*System.out.println("[DEBUG] 开始构建SQL查询...");*/

        // 处理价格范围条件
        if (priceRange != null && !priceRange.isEmpty() && !"不限".equals(priceRange)) {
           /* System.out.println("[DEBUG] 添加价格筛选条件: " + priceRange);*/

            if ("1".equals(priceRange)) {
                sql.append(" AND price < ?");
                params.add(new BigDecimal("1000000"));
            } else if ("2".equals(priceRange)) {
                sql.append(" AND price BETWEEN ? AND ?");
                params.add(new BigDecimal("1000000"));
                params.add(new BigDecimal("2000000"));
            }
            else if ("3".equals(priceRange)) {
                sql.append(" AND price BETWEEN ? AND ?");
                params.add(new BigDecimal("2000000"));
                params.add(new BigDecimal("3000000"));
            } else if ("4".equals(priceRange)) {
                sql.append(" AND price > ?");
                params.add(new BigDecimal("3000000"));
            }
        }

        // 处理面积范围条件
        if (areaRange != null && !areaRange.isEmpty() && !"不限".equals(areaRange)) {
            /*System.out.println("[DEBUG] 添加面积筛选条件: " + areaRange);*/

            if ("1".equals(areaRange)) {
                sql.append(" AND area < ?");
                params.add(new BigDecimal("60"));
            } else if ("2".equals(areaRange)) {
                sql.append(" AND area BETWEEN ? AND ?");
                params.add(new BigDecimal("60"));
                params.add(new BigDecimal("90"));
            } else if ("3".equals(areaRange)) {
                sql.append(" AND area BETWEEN ? AND ?");
                params.add(new BigDecimal("90"));
                params.add(new BigDecimal("120"));
            } else if ("4".equals(areaRange)) {
                sql.append(" AND area > ?");
                params.add(new BigDecimal("120"));
            }
        }

        // 处理位置条件
        if (location != null && !location.isEmpty() && !"不限".equals(location)) {
           /* System.out.println("[DEBUG] 添加位置筛选条件: " + location);*/
            sql.append(" AND (house_name LIKE ? OR building_number LIKE ?)");
            params.add("%" + location + "%");
            params.add("%" + location + "%");
        }

        /*System.out.println("[DEBUG] 最终生成的 SQL 语句: " + sql.toString());
        System.out.print("[DEBUG] 参数列表: ");
        for (Object param : params) {
            System.out.print(param + ", ");
        }
        System.out.println();*/

        // 执行查询
        List<HouseInfo> houses = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                int count = 0;
                while (rs.next()) {
                    HouseInfo house = new HouseInfo();
                    house.setHouseId(rs.getInt("house_id"));
                    house.setHouseName(rs.getString("house_name"));
                    house.setBuildingNumber(rs.getString("building_number"));
                    house.setUnitNumber(rs.getString("unit_number"));
                    house.setRoomNumber(rs.getString("room_number"));
                    house.setArea(rs.getBigDecimal("area"));
                    house.setPrice(rs.getBigDecimal("price"));
                    house.setStatus(rs.getInt("status"));
                    house.setBuyerId(rs.getInt("buyer_id"));
                    house.setHouseImage(rs.getString("house_image"));
                    houses.add(house);
                    count++;
                }

                /*System.out.println("[DEBUG] 查询结果数量: " + count + " 条");*/
            }
        }

        return houses;
    }
}
